package com.ftg.learn.bus.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import javax.swing.filechooser.FileSystemView;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

public class ToExcel {



    public List<T> objectAssignment(T t, List<Map<String,Object>> list) throws InstantiationException, IllegalAccessException, IntrospectionException, InvocationTargetException {
        //传来的表格数据 key-value
        Class cl= t.getClass();//改成对应pojo类
        List<T> lob=new ArrayList<>();
        for(int j=0;j<list.size();j++){
            T t1 = (T)cl.newInstance();
            Field[] fields=cl.getDeclaredFields();
            for(Field field:fields){
                String fieldName=field.getName();
                PropertyDescriptor pd = new PropertyDescriptor(fieldName, cl);
                Method wM = pd.getWriteMethod();
                for(int i=0;i<list.get(j).size();i++){
                    wM.invoke(t1, list.get(j).get(fieldName));
                }
            }
            lob.add(t1);
        }
        return lob;
    }

    /**
     * 导出Excel表格
     * @param url                   保存地址(默认桌面)
     * @param fileName              文件名称
     * @param dataFromDatebase      数据源
     * @param ColumnNameAndComment  列名和注释
     */
    public  void WriteExcel(String url,String fileName,List<Map<String,Object>> dataFromDatebase,List<Map<String,Object>> ColumnNameAndComment) throws IOException {
        FileSystemView fsv = FileSystemView.getFileSystemView();
        File com=fsv.getHomeDirectory();
            url=com.getPath()+"\\"+fileName+".xlsx";
            Workbook wb = new HSSFWorkbook();//新建一个工作簿
            OutputStream os = new FileOutputStream(url);
            List<Map<String, Object>> list = dataFromDatebase;
            Sheet sheet = wb.createSheet("一个sheet页");//创建一个sheet页
            //第一列字段名
            int i = 0;
            while (i == 0){
                Iterator<Map<String, Object>> itCloumns = ColumnNameAndComment.iterator();
                Row rowCloumn = sheet.createRow(i); // 创建一个行
                int j = 0;
                while (itCloumns.hasNext()) {
                    Iterator<String> itCloumn = itCloumns.next().keySet().iterator();
                    Cell cell = rowCloumn.createCell(j); // 创建一个单元格
                    String data=ColumnNameAndComment.get(j++).get(itCloumn.next())+"";
                    for(int o=0;o<ColumnNameAndComment.size();o++){
                        if(ColumnNameAndComment.get(o).get("COLUMN_NAME").equals(data)){
                            cell.setCellValue(ColumnNameAndComment.get(o).get("COLUMN_COMMENT")+"");
                            continue;
                        }
                    }
                }
                i++;
            }

            //第二里开始输入数据
            for (i = 0; i < list.size(); i++) {
                Iterator<Map<String, Object>> itCloumns = ColumnNameAndComment.iterator();
                Row rowCloumn = sheet.createRow(i+1); // 创建一个行
                int j = 0;
                while (itCloumns.hasNext()) {
                    Cell cell = rowCloumn.createCell(j++); // 创建一个单元格
                    String clounnNext=itCloumns.next().get("COLUMN_NAME")+"";
                    cell.setCellValue(list.get(i).get(clounnNext)==null?"":list.get(i).get(clounnNext).toString());
                }
            }
            wb.write(os);
            os.close();

    }


    /**
     * 导入数据
     * @param url
     * @throws IOException
     */
    public List<Map<String,Object>> toLeadExcel(String url,List<Map<String,Object>> ColumnNameAndComment) throws IOException {
        File file = new File(url);
        //获得该文件的输入流
        FileInputStream stream = new FileInputStream(file);
        // 多态  抛异常
        Workbook sheets = new HSSFWorkbook(stream);
        List<String> COLUMN_NAME=new ArrayList<>();
        //获取一个工作表(sheet页)，下标从0开始
        Sheet sheet = sheets.getSheetAt(0);
        // 获取第一行数据  与数据库的列名对应
        for(int i =0; i<1 ; i++){
            // 获取行数
            Row row = sheet.getRow(i);
            int cloumnCount=row.getPhysicalNumberOfCells();
            // 获取单元格 取值
            int j=0;
            while (j<cloumnCount){
                String value = row.getCell(j).getStringCellValue();
                for(int x=0;i<ColumnNameAndComment.size();x++){
                    if(ColumnNameAndComment.get(x).get("COLUMN_COMMENT").equals(value)){
                        COLUMN_NAME.add(ColumnNameAndComment.get(x).get("COLUMN_NAME").toString());
                        break;
                    }
                }
                j++;
            }
        }
        List<Map<String,Object>> list=new ArrayList<>();
        //获取第二行开始的所有数据
        for ( int i = 1; i<=sheet.getLastRowNum() ; i++) {
            // 获取行数
            Row row = sheet.getRow(i);
            int cloumnCount=row.getPhysicalNumberOfCells();
            // 获取单元格 取值
            int j=0;
            Map<String,Object> map=new HashMap<>();
            while (j<cloumnCount){
                String value = row.getCell(j).getStringCellValue();
                String key=COLUMN_NAME.get(j);
                map.put(key,value);
                j++;
            }
            list.add(map);
        }
        //关流
        sheets.close();
        stream.close();
        return list;
    }

}
